<?php
/**
 * polwebgis
 * 
 * This file is part of polwebgis.
 *
 * @copyright   Copyright (c) 2012 Dominik Janisch (http://www.joanneum.at)
 * @lastmodified    $LastChangedDate: 2012-04-03 09:23:39 +0000 (Tue, 03 Apr 2012) $
 * @revision    $Revision: 174 $ 
 */

class GisMapGateway extends TableDataGateway
{           

    private $where_condition_ = '';
        
    public function __construct()
    {
        $this->establishConnection();
    }
        
    public function __destruct(){}
    
    public function getDataDetails($map_id)
    {
        $stmt = $this->database_connection_->newStatement("select dt_tabelle, dt_beschreibung, dt_titel, dt.e_id, srid as e_projection, e_name, e_bbox, e_resolution, e_unit, g_name, dt.t_id, t_name from daten_tabellen as dt, ebene as e, gebiet as g, geometry_columns as gc, thema as t where dt.t_id=t.t_id and dt_id=:map_id and dt.g_id=g.g_id and dt.e_id=e.e_id and f_table_name='shape_' || e.e_id");
        $stmt->bindValue(':map_id', $map_id, PDO::PARAM_INT);
        $stmt->execute();
        while($row = $stmt->fetch(PDO::FETCH_OBJ))
            return $row;
        return false;
    }
    
    public function getAvailableLayers($srid, $e_id)
    {
        $stmt = $this->database_connection_->newStatement("select e_name as l_name, f_table_name from geometry_columns as gc, ebene as e where srid=:srid and f_table_name<>'shape_" . pg_escape_string((int)$e_id) ."' and split_part(f_table_name, 'shape_', 2)=e.e_id::text UNION select o_name as l_name, f_table_name from geometry_columns as gc, overlay as o where srid=:srid and f_table_name like 'overlay_%' and split_part(f_table_name, 'overlay_', 2)=o.o_id::text");
        $stmt->bindValue(':srid', $srid, PDO::PARAM_INT);
        $stmt->execute();
        $arr_return = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            $arr_return[$row['f_table_name']] = $row['l_name'];
        return $arr_return;
    }
    
    public function createWhereCondition($table_columns)
    {
        $cnt=0;
        foreach($table_columns as $key => $value)
        {
            if($value != '-1')
            {
                if($cnt++ > 0)
                    $this->where_condition_ .= " and ";    
                $this->where_condition_ .= pg_escape_string($key) . "='" .pg_escape_string($value)."'";
            }  
        }
    }
    
    public function getWhereCondition()
    {
        return $this->where_condition_;
    }
    
    public function getColumnNamesFromTablesWithSchema($arr_values, $data_table)
    {
        $stmt = $this->database_connection_->newStatement("select column_name from information_schema.columns where table_schema=:schema and table_name=:table and column_name NOT IN ('gid', 'cases')");
        $stmt->bindValue(':schema', $arr_values[0], PDO::PARAM_STR);
        $stmt->bindValue(':table', $arr_values[1], PDO::PARAM_STR);
        $stmt->execute();
        $arr_return = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            $arr_return[ucfirst($row['column_name'])] = $this->getColumnValues($row['column_name'], $data_table);
        return $arr_return;
    }
    
    public function getColumnNamesFromTablesWithoutSchema($data_table)
    {
        $stmt = $this->database_connection_->newStatement("select column_name from information_schema.columns where table_name=:table and column_name NOT IN ('gid', 'cases')");
        $stmt->bindValue(':table', $data_table, PDO::PARAM_STR);
        $stmt->execute();
        $arr_return = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            $arr_return[ucfirst($row['column_name'])] = $this->getColumnValues($row['column_name'], $data_table);
        return $arr_return;
    }
    
    public function getDataTypeFromTablesWithSchema($arr_values)
    {
        $stmt = $this->database_connection_->newStatement("select data_type from information_schema.columns where table_schema=:schema and table_name=:table and column_name ='cases'");
        $stmt->bindValue(':schema', $arr_values[0], PDO::PARAM_STR);
        $stmt->bindValue(':table', $arr_values[1], PDO::PARAM_STR);
        $stmt->execute();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            return $row['data_type'];
        return false;
    }
    
    public function getDataTypeFromTablesWithoutSchema($table_name)
    {
        $stmt = $this->database_connection_->newStatement("select data_type from information_schema.columns where table_name=:table and column_name ='cases'");
        $stmt->bindValue(':table', $table_name, PDO::PARAM_STR);
        $stmt->execute();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            return $row['data_type'];
        return false;
    }
    
    public function getColumnValues($column, $table)
    {
        $stmt = $this->database_connection_->newStatement("select ".$column." as column from ".$table." group by ".$column." order by ".$column." asc");
        $stmt->execute();
        $arr_return = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            $arr_return[] = $row['column'];
        return $arr_return;
    }
    
    public function getCasesDimensions($table)
    {
        $statement = "SELECT min(cases) as min, max(cases) as max, count(*) as cases, stddev_pop(cases) as stddev, sum(cases)/count(*) as mean FROM ";
        $statement .= "(SELECT gid, sum(cases) as cases from ".$table;
        if(strlen($this->where_condition_) > 0)
            $statement .= " where ".$this->where_condition_;
        $statement .= " group by gid) as tmp";
        $stmt = $this->database_connection_->newStatement($statement);
        $stmt->execute();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            return $row;
        return false;
    }
    
    public function getMoranCasesDimensions($table, $e_id)
    {
        $statement = "select min(cases) as min, max(cases) as max, count(*) as cases, stddev_pop(cases) as stddev, sum(cases)/count(*) as mean, sum(cases) as cases from (SELECT s.gid, case when cases is not null then cases else 0 end as cases FROM shape_".pg_escape_string($e_id)." as s LEFT JOIN ".pg_escape_string($table)." as d ON (d.gid=s.gid)";
        if(strlen($this->where_condition_) > 0)
            $statement .= " where ".$this->where_condition_;
        $statement .= " group by s.gid, case when cases is not null then cases else 0 end) as tmp";
        $stmt = $this->database_connection_->newStatement($statement);
        $stmt->execute();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            return $row;
        return false;
    }
    
    public function getMapCases($table)
    {
        $statement = "SELECT cases FROM (SELECT gid, sum(cases) as cases FROM ".$table;
        if(strlen($this->where_condition_) > 0)
            $statement .= " where ".$this->where_condition_;
        $statement .= " group by gid) as tmp order by cases";
        $stmt = $this->database_connection_->newStatement($statement);
        $stmt->execute();
        $arr_return = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            $arr_return[] = $row['cases'];
        return $arr_return;
    }
    
    public function getMapCasesAndRegion($table, $e_id)
    {
        $statement = "SELECT region, sum(cases) as cases FROM ".pg_escape_string($table)." as d, shape_".pg_escape_string($e_id)." as s where d.gid=s.gid";
        if(strlen($this->where_condition_) > 0)
            $statement .= " and ".$this->where_condition_;
        $statement .= " group by region order by cases";
        $stmt = $this->database_connection_->newStatement($statement);
        $stmt->execute();
        $arr_return = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $arr_tmp = array();
            $arr_tmp['value'] = $row['cases'];
            $arr_tmp['region'] = $row['region'];
            $arr_return[] = $arr_tmp;
        }
        return $arr_return;
    }
    
    public function getMoranData($table, $e_id)
    {
        $statement = "select region, gid, sum(cases) as cases from (SELECT region, s.gid, case when cases is not null then cases else 0 end as cases FROM shape_".pg_escape_string($e_id)." as s LEFT JOIN ".pg_escape_string($table)." as d ON (d.gid=s.gid)";
        if(strlen($this->where_condition_) > 0)
            $statement .= " where ".$this->where_condition_;
        $statement .= " group by region, s.gid, case when cases is not null then cases else 0 end) as tmp group by region, gid order by cases";
        $stmt = $this->database_connection_->newStatement($statement);
        $stmt->execute();
        $arr_return = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $arr_return[] = $row;
        }
        return $arr_return;
    }
    
    public function calculateQuantileClasses($table, $limit, &$arr_gids, &$legend_max, &$legend_min)
    {
        $statement = "SELECT gid, cases FROM (SELECT gid, sum(cases) as cases FROM ".$table;
        if(strlen($this->where_condition_) > 0)
        {
            $statement .= " where ".$this->where_condition_;
            if(count($arr_gids) > 0)
                $statement .= " and gid NOT IN (". implode($arr_gids, ',').")";
        }
        else
        {
            if(count($arr_gids) > 0)
                $statement .= " where gid NOT IN (". implode($arr_gids, ',').")";
        }
        $statement .= " group by gid) as tmp  order by cases limit ".pg_escape_string($limit);
        $stmt = $this->database_connection_->newStatement($statement);
        $stmt->execute();
        $row_cnt=0;
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            if($row['cases'] > $legend_max)
                $legend_max = $row['cases'];
            if($row['cases'] < $legend_min)
                $legend_min = $row['cases'];
            ++$row_cnt;
            $arr_gids[] = $row['gid']; 
        }
        return $row_cnt;
    }
    
    public function getChartData($arr_query_columns, $table_details, &$arr_values, $is_int, $max, $format_handler, $sort_order, $datastructure)
    {
        $statement = "SELECT dt.gid, region, region_id, max, sum(cases) as cases, ((sum(cases)/max::float)*500)::int as share"; 
        if(count($arr_query_columns) > 0)
            $statement .= ", " . strtolower(implode(",", $arr_query_columns));
        $statement .=" FROM ".$table_details->dt_tabelle." as dt, shape_".$table_details->e_id." as s, (select max(cases) as max from ".$table_details->dt_tabelle;
        if(strlen($this->where_condition_) > 0)
            $statement .= " where ".$this->where_condition_;
        $statement .=") as c where dt.gid=s.gid";
        if(strlen($this->where_condition_) > 0)
            $statement .= " and ".$this->where_condition_;
        $statement .= " group by dt.gid, region, region_id, max"; 
        if(count($arr_query_columns) > 0)
            $statement .= ", " . strtolower(implode(",", $arr_query_columns));
        $statement .= " order by";
        if($sort_order == 2)
            $statement .= " cases desc,";
        $statement .= " region_id";
        if(count($arr_query_columns) > 0)
            $statement .= ", " . strtolower(implode(",", $arr_query_columns));
        $stmt = $this->database_connection_->newStatement($statement);
        $stmt->execute();
        $cnt = 0; 
        while($row = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            if($datastructure == 1)
            {
                $arr_values[$row['region']][$cnt]['cases'] = $format_handler->getOutput($row['cases'], $is_int);
                $arr_values[$row['region']][$cnt]['share'] = $row['share'];
                $arr_values[$row['region']][$cnt]['region_id'] = $row['region_id'];
                foreach($arr_query_columns as $value)
                    $arr_values[$row['region']][$cnt]['dimension'][$value] = $row[strtolower($value)];
                ++$cnt;
            }
            else
            {
                $arr_values[$cnt]['cases'] = $format_handler->getOutput($row['cases'], $is_int);
                $arr_values[$cnt]['share'] = $row['share'];
                $arr_values[$cnt]['region_id'] = $row['region_id'];
                $arr_values[$cnt]['region'] = $row['region'];
                foreach($arr_query_columns as $value)
                    $arr_values[$cnt]['dimension'][$value] = $row[strtolower($value)];
                ++$cnt;
            }
        }
    }
}

?>
